package com.example;

import java.sql.*;
import java.util.Scanner;

public class homework {
    public static final String URL = "jdbc:mysql://localhost:3306/test3?serverTimezone=GMT";
    public static final String USER = "root";
    public static final String PASSWORD = "1234";

    public static void testQuery(){
        try(Connection connection = DriverManager.getConnection(URL,USER,PASSWORD);) {
            Statement statement = connection.createStatement();
            String sql = "select * from student";
            ResultSet res1 = statement.executeQuery(sql);
            // 遍历结果集
            while(res1.next()){
                // 读取每一列数据
                int sid = res1.getInt("sid");
                String sname = res1.getString("sname");
                int age = res1.getInt("age");
                String gender = res1.getString("gender");
                String address = res1.getString("address");
                System.out.println("id:" + sid + "  name:" + sname + "  age:" + age + "  gender:" + gender + "  address:" + address);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }

    public static void testinsert(String sname,int age,String gender,String address){
        try(Connection connection = DriverManager.getConnection(URL,USER,PASSWORD);) {
            String sql = "insert into stu(sname,age,gender,address) values(?,?,?,?)";
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            // 设置参数
            preparedStatement.setString(1,sname);
            preparedStatement.setInt(2,age);
            preparedStatement.setString(3,gender);
            preparedStatement.setString(4,address);
            // 执行命令
            int rows = preparedStatement.executeUpdate();
            if (rows > 0 ){
                System.out.println("插入成功！");
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }

    public static void testdelete(int sid){
        try(Connection connection = DriverManager.getConnection(URL,USER,PASSWORD);) {
            String sql = "delete from student where sid = ?";
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            // 设置参数
            preparedStatement.setInt(1,sid);
            // 执行命令
            int rows = preparedStatement.executeUpdate();
            if (rows > 0 ){
                System.out.println("删除成功！");
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }
    public static void testupdate(int sid,String sname,int age,String gender,String address){
        try(Connection connection = DriverManager.getConnection(URL,USER,PASSWORD);) {
            String sql = "update student set sname = ?,age = ?,gender = ?,address = ? where id = ?";
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            // 设置参数
            preparedStatement.setString(1,sname);
            preparedStatement.setInt(2,age);
            preparedStatement.setString(3,gender);
            preparedStatement.setString(4,address);
            preparedStatement.setInt(5,sid);
            // 执行命令
            int rows = preparedStatement.executeUpdate();
            if (rows > 0 ){
                System.out.println("更新成功！");
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }

    public static void main(String[] args) {
        while(true){
            System.out.println("-------------------------------------");
            System.out.println(" 1) 打印学生信息");
            System.out.println(" 2) 添加学生");
            System.out.println(" 3) 删除学生");
            System.out.println(" 4) 更新学生");
            System.out.println(" 5) 退出");
            System.out.println("-------------------------------------");
            System.out.println(" 请选择要进行的操作:");
            Scanner sc = new Scanner(System.in);
            int choose = sc.nextInt();
            if (choose == 1){
                testQuery();
            }
            if (choose == 2){
                System.out.println("请输入要添加的学生的信息:");
                System.out.println("姓名:");
                String sname = sc.next();
                System.out.println("年龄:");
                int age = sc.nextInt();
                System.out.println("性别:");
                String gender = sc.next();
                System.out.println("住址:");
                String address = sc.next();
                testinsert(sname,age,gender,address);
            }
            if (choose == 3){
                System.out.println("请输入要删除学生的学号:");
                int sid = sc.nextInt();
                testdelete(sid);
            }
            if (choose == 4){
                System.out.println("请输入要更新学生的学号:");
                int sid = sc.nextInt();
                System.out.println("请输入要学生更新后的信息:");
                System.out.println("姓名:");
                String sname = sc.next();
                System.out.println("年龄:");
                int age = sc.nextInt();
                System.out.println("性别:");
                String gender = sc.next();
                System.out.println("住址:");
                String address = sc.next();
                testinsert(sname,age,gender,address);
                testupdate(sid,sname,age,gender,address);
            }
            if (choose == 5){
                return;
            }

        }
    }
}
